﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using LibrarySolution.DB;
using LibrarySolution.Model.基础;

namespace LibrarySolution.DAO.基础
{
    class YhJbDao
    {
        String sql;
        List<SqlParameter> list;
        DataTable dt = new DataTable();
        //private static StringBuilder SqlBuilder = new StringBuilder(
        //    "select yhjb_code as 级别编码, yhjb_name as 级别名称,yhjb_no as 级别编号," +
        //    "yhjb_jyqx as 借阅期限,yhjb_xjqx as 续借期限 from jc_yhjb");

        public DataTable getDataTable(JbxxDetail jbxxDetail)
        {
            JbxxQuery(jbxxDetail);
            dt = DBExecuteUtil.querySqlGetTable(sql, null);
            return dt;
        }

        public void DelSelectRow(string jbCode)
        {
            String sql = "delete from jc_yhjb where yhjb_code = @jbCode";
            SqlParameter[] parameters = { new SqlParameter("@jbCode", jbCode) };
            DBExecuteUtil.querySqlV(sql, parameters);
        }

        public void updateJbxx(JbxxDetail jbxxDetail)
        {
            JbxxQuery(jbxxDetail);
            sql = "update jc_yhjb set yhjb_name = @jbName, yhjb_no = @jbNo," +
                "yhjb_jyqx = @Jyqx,yhjb_xjqx = @Xjqx where yhjb_code = @jbCode";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public void insertJbxx(JbxxDetail jbxxDetail)
        {
            JbxxQuery(jbxxDetail);
            sql = "insert into jc_yhjb values (@jbName,@jbNo,@Jyqx,@Xjqx)";
            DBExecuteUtil.querySqlV(sql, list.ToArray());
        }

        public String getMaxJbCode()
        {
            String result;
            sql = "select max(yhjb_code) from jc_yhjb";
            result = DBExecuteUtil.querySql(sql, null);
            return Convert.ToString(int.Parse(result) + 1);
        }

        public JbxxDetail getJbxxByCode(JbxxDetail jbxxDetail)
        {
            String[][] result;
            JbxxQuery(jbxxDetail);
            result = DBExecuteUtil.querySqlArray(sql, list.ToArray());
            if (result != null)
            {
                jbxxDetail.yhjb_code = result[0][0];
                jbxxDetail.yhjb_name = result[0][1];
                jbxxDetail.yhjb_no = result[0][2];
                jbxxDetail.jyqx = result[0][3];
                jbxxDetail.xjqx = result[0][4];
            }
            return jbxxDetail;
        }

        // 公共查询语句
        private void JbxxQuery(JbxxDetail jbxxDetail)
        {
            list = new List<SqlParameter>();
            sql = "select yhjb_code as 级别编码, yhjb_name as 级别名称,yhjb_no as 级别编号," +
            "yhjb_jyqx as 借阅期限,yhjb_xjqx as 续借期限 from jc_yhjb";
            if (jbxxDetail.yhjb_code != null)
            {
                list.Add(new SqlParameter("@jbCode", jbxxDetail.yhjb_code));
                sql += " where yhjb_code = @jbCode";

                if (jbxxDetail.yhjb_name != null)
                {
                    list.Add(new SqlParameter("@jbName", jbxxDetail.yhjb_name));
                    sql += " and yhjb_name = @jbName";
                }
                if (jbxxDetail.yhjb_no != null)
                {
                    list.Add(new SqlParameter("@jbNo", jbxxDetail.yhjb_no));
                    sql += " and yhjb_no = @jbNo";
                }
                if (jbxxDetail.xjqx != null)
                {
                    list.Add(new SqlParameter("@Xjqx", jbxxDetail.xjqx));
                    sql += " and yhjb_xjqx = @Xjqx";
                }
                if (jbxxDetail.jyqx != null)
                {
                    list.Add(new SqlParameter("@Jyqx", jbxxDetail.jyqx));
                    sql += " and yhjb_jyqx = @Jyqx";
                }
            }
        }
    }
}
